<?php

namespace App\Console\Commands;

use App\Models\Warranty;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

set_time_limit(0);
ini_set('memory_limit', '128M');

class WarrantyStatistics extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:WarrantyStat';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';
//sara 2025.5.14前计算分值
    // protected const TYPE_SCORE = [
    //     'MPD'   => 1.5,
    //     'LUX'   => 1,
    //     'PROTEX'=> 1,
    //     'ARES'  => 0.66,
    //     'X5'    => 5,
    //     'X2'    => 3,
    //     'X BLACK'   => 1.5,
    //     'AP'        => 1,
    //     'GL'        => 0.33,
    //     '6米'       => 0.5,
    //     '15米'      => 1.5,
    // ];
        protected const TYPE_SCORE = [
        'MPD'   => 1.5,
        'LUX'   => 1,
        'PROTEX'=> 0.2,
        'ARES'  => 0.8,
        'EX12'  => 0.5,
        'EX15'  => 0.6,
        'X5'    => 5,
        'X2'    => 3,
        'X BLACK'   => 1.5,
         'XP20'   => 5,
         'XP40'   => 5,
         'XP55'   => 5,
         'XP70'   => 5,
         'XP70 184'   => 6,
         'XP70 90'   =>3,
         'X BLUE 80 '   => 1,
        'AP'        => 1,
        'GL'        => 0.33,
        '6米'       => 0.5,
        '15米'      => 1.5,
        'ASP-M'     => 0.666,
        'ASP-A'     => 1,
        'ASP-M+'    => '',
        'ASP-M+ 哑光膜' => 1,
        'ASP-M PLUS' => 0.666,
        'ASP-M+ 122' => 0.53,
    ];
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        /*$start_date = $this->argument('start_date');
        $end_date = $this->argument('end_date');*/
        //$this->statOldData('2020-12', '2021-01');
        $this->statOldData(date('Y-m-d', strtotime('-1 day')), date('Y-m-d'));
        return 0;
    }

    public function statOldData($start_month, $end_month)
    {
        $start_month = strtotime($start_month);
        $end_month = strtotime($end_month);
        for ($time = $start_month; $time < $end_month; $time += 86400) {
            $start_time = date('Y-m-d', $time);
            echo $start_time; echo PHP_EOL;
            $end_date = date('Y-m-d', $time + 86400);
            $sql = 'SELECT group_concat(warranty.id) AS warranty_id, count(1) AS total, user_id, users.user_type, distributor_id AS creator_id, approved_date FROM warranty LEFT JOIN users ON warranty.user_id=users.id WHERE approved = 1 AND warranty.approved_date>="' . $start_time . '" AND warranty.approved_date<"' . $end_date . '" AND warranty.deleted_at IS NULL GROUP BY user_id';
            echo $sql; echo PHP_EOL;
            $warranty = DB::select($sql);
            if (empty($warranty)) {
                continue;
            }

            $this->statRollScore($warranty);
            foreach ($warranty as $item) {
                $sql = 'SELECT rm.product_type_id, rm.id as roll_id, fm.id as film_type_id, fm.product_type_id, warranty_id, fm.english_value FROM warranty_to_vehicle_coverage as wc LEFT JOIN roll_master AS rm ON wc.roll_number=rm.roll_number LEFT JOIN film_type_master as fm ON wc.film_type_id=fm.id WHERE wc.deleted_at IS NULL AND warranty_id IN (' . $item->warranty_id . ')';
                $sql .= ' GROUP BY fm.product_type_id, warranty_id';
                $result = DB::select($sql);
                echo $sql; echo PHP_EOL;
                if (empty($result)) {
                    continue;
                }
                $roll_1 = 0;
                $roll_2 = 0;
                $roll_3 = 0;
                $part = 0;
                $full = 0;
                foreach ($result as $value) {
                    $start_date = $item->approved_date;
                    if (empty($value->roll_id) || empty($value->english_value)) {
                        continue;
                    }

                    $per_score = $this->getRollScore($value->english_value);

                    $num = 1;
                    $score = round($num * $per_score, 2);

                    $roll_1 += $value->product_type_id == 1 ? 1 : 0;
                    $roll_2 += $value->product_type_id == 2 ? 1 : 0;
                    $roll_3 += $value->product_type_id == 3 ? 1 : 0;

                    $rollStat = DB::table('xpel_warranty_roll_stat')->where('dealer_id', $item->user_id)
                        ->where('roll_id', $value->roll_id)
                        ->where('create_date', $start_date)
                        ->first();
                    if (empty($rollStat)) {
                        DB::table('xpel_warranty_roll_stat')
                            ->insert([
                                'dealer_id'   => $item->user_id,
                                'dist_id'   => $item->user_type == 3 ? $item->creator_id : $item->user_id,
                                'roll_id'   => $value->roll_id,
                                'roll_type_id'  => $value->film_type_id,
                                'product_type_id'   => $value->product_type_id,
                                'create_date' => $start_date,
                                'total' => 1,
                                'score' => $score,
                            ]);
                    } else {
                        DB::table('xpel_warranty_roll_stat')->where('id', $rollStat->id)
                            ->update([
                                'total' => $rollStat->total + 1,
                                'score' => $rollStat->score + $score,
                            ]);
                    }
                }

                $sql = 'SELECT part_id, warranty_id FROM warranty_to_vehicle_coverage as wc WHERE wc.deleted_at IS NULL AND warranty_id IN (' . $item->warranty_id . ')';
                $sql .= ' GROUP BY part_id, warranty_id';

                $result = DB::select($sql);

                foreach ($result as $value) {
                    $part += !in_array($value->part_id, [17, 18, 150, 152, 40, 41]) ? 1 : 0;
                    $full += in_array($value->part_id, [17, 18, 150, 152, 40, 41]) ? 1 : 0;
                }

                $insertData = [
                    'dealer_id'   => $item->user_id,
                    'dist_id'   => $item->user_type == 3 ? $item->creator_id : $item->user_id,
                    'roll_1'      => $roll_1,
                    'roll_2'      => $roll_2,
                    'roll_3'      => $roll_3,
                    'part'        => $part,
                    'full'        => $full,
                    'create_date' => $start_date,
                    'order_num'   => 0,
                    'total'       => $item->total,
                ];
                DB::table('xpel_warranty_stat')->insert($insertData);
                unset($result);
            }
            unset($warranty);
        }
    }

    public function statRollScore($warranty)
    {
        foreach ($warranty as $item) {
            $sql = 'SELECT fm.id as film_type_id, fm.product_type_id, fm.english_value, wc.length, rm.length as rm_length FROM warranty_to_vehicle_coverage as wc LEFT JOIN roll_master AS rm ON wc.roll_number=rm.roll_number LEFT JOIN film_type_master as fm ON wc.film_type_id=fm.id WHERE wc.deleted_at IS NULL AND warranty_id IN (' . $item->warranty_id . ')';
            $result = DB::select($sql);
            if (empty($result)) {
                continue;
            }
            foreach ($result as $value) {
                $start_date = date('Y-m-d', strtotime($item->approved_date));
                if (empty($value->english_value) || empty($value->rm_length)) {
                    continue;
                }

                $per_score = $this->getRollScore($value->english_value);

                if (in_array($value->film_type_id, [27, 74])) {
                    $num = round($value->length / 1500, 2);
                } else {
                    $num = round($value->length / $value->rm_length, 2);
                }

                $score = $num * $per_score;
                $rollStat = DB::table('xpel_warranty_roll_detail_stat')
                    ->where('dealer_id', $item->user_id)
                    ->where('film_type_id', $value->film_type_id)
                    ->where('create_date', $start_date)
                    ->first();
                if (empty($rollStat)) {
                    DB::table('xpel_warranty_roll_detail_stat')
                        ->insert([
                            'dealer_id'   => $item->user_id,
                            'dist_id'   => $item->user_type == 3 ? $item->creator_id : $item->user_id,
                            'film_type_id'  => $value->film_type_id,
                            'product_type_id'   => $value->product_type_id,
                            'create_date' => $start_date,
                            'num' => $num,
                            'score' => $score,
                        ]);
                } else {
                    DB::table('xpel_warranty_roll_detail_stat')->where('id', $rollStat->id)
                        ->update([
                            'num' => $rollStat->num + $num,
                            'score' => $rollStat->score + $score,
                        ]);
                }
            }
        }
    }

    public function statOrderNum($user_id, $start_date)
    {
        $sql = 'SELECT sum(roll_sales.length) AS total_length, roll_id, roll_master.length as master_length, roll_master.product_type_id, roll_master.roll_number FROM roll_sales LEFT JOIN roll_master ON roll_sales.roll_id=roll_master.id WHERE sold_to_user_id=' . $user_id . ' AND roll_sales.created_at<="' . $start_date . '" GROUP BY roll_id';
        $sale_data = DB::select($sql);
        if (empty($sale_data)) {
            return 0;
        }

        $stock = 0;
        foreach ($sale_data as $sale) {
            if ($sale->master_length <= 0) {
                continue;
            }
            $roll_length = $sale->total_length;
            if (strtotime($start_date) > strtotime('2020-12-15')) {
                $sql = 'SELECT sum(length) AS transfer_len FROM roll_transfer WHERE roll_id=' . $sale->roll_id . ' AND transfer_by_user_id=' . $user_id . ' AND approved=1 AND approved_date<="' . $start_date . '"' ;
                $transfer_by_data = DB::select($sql);
                $roll_length = $roll_length - ($transfer_by_data && $transfer_by_data[0] ? $transfer_by_data[0]->transfer_len : 0);

                $sql = 'SELECT sum(length) AS transfer_len FROM roll_transfer WHERE roll_id=' . $sale->roll_id . ' AND  transfer_to_user_id=' . $user_id . ' AND approved=1 AND approved_date<="' . $start_date . '"' ;
                $transfer_to_data = DB::select($sql);
                $roll_length = $roll_length + ($transfer_to_data && $transfer_to_data[0] ? $transfer_to_data[0]->transfer_len : 0);

                $sql = 'SELECT sum(length) AS return_len FROM roll_return WHERE roll_id=' . $sale->roll_id . ' AND  user_id=' . $user_id . ' AND status=1 AND created_at<="' . $start_date . '"' ;
                $return_data = DB::select($sql);
                $roll_length = $roll_length - ($return_data && $return_data[0] ? $return_data[0]->return_len : 0);
            }

            $stock =round($roll_length / $sale->master_length, 2);
        }
        unset($sale_data);

        return $stock;
    }

    /**
     * 获取卷号的分数
     *
     * @param string $name
     * @return int|mixed
     */
    public function getRollScore(string $name)
    {
        $score = 0;
        foreach (self::TYPE_SCORE as $key => $item) {
            if (mb_strpos($name, $key) === 0) {
                $score = $item;
                break;
            }
        }

        return $score;
    }
}
